// ============================================================================
//
// Copyright (C) 2006-2007 Dengues
//
// Google Group: http://groups.google.com/group/dengues
// QQ Group: 24885404
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
//
// ============================================================================
package org.dengues.core.metadata;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.dengues.commons.sql.data.DataSetRow;
import org.dengues.core.DenguesCorePlugin;
import org.dengues.model.DenguesModelManager;
import org.dengues.model.database.DBColumn;
import org.dengues.model.database.DBTable;
import org.dengues.model.database.DatabaseDiagram;
import org.dengues.model.database.ForeignKey;
import org.dengues.model.database.ForeignKeyLabel;
import org.eclipse.core.runtime.IProgressMonitor;
import org.eclipse.core.runtime.NullProgressMonitor;
import org.eclipse.core.runtime.SubProgressMonitor;
import org.eclipse.emf.common.util.EList;

/**
 * Qiang.Zhang.Adolf@gmail.com class global comment. Detailled comment <br/>
 * 
 * $Id: Dengues.epf 2007-12-14 qiang.zhang $
 * 
 */
public class MetadataUtils {

    public static final String FK_NAME = "FK_NAME";

    private static final String PKCOLUMN_NAME = "PKCOLUMN_NAME";

    private static final String PKTABLE_NAME = "PKTABLE_NAME";

    private static final String FKCOLUMN_NAME = "FKCOLUMN_NAME";

    private static final String COLUMN_NAME = "COLUMN_NAME";

    private static Logger log = Logger.getLogger(MetadataUtils.class);

    private static final String TABLETYPE_TABLE = "TABLE"; //$NON-NLS-1$

    private static final String TABLETYPE_VIEW = "VIEW"; //$NON-NLS-1$

    private static final String TABLETYPE_SYNONYM = "SYNONYM"; //$NON-NLS-1$

    private static final int TYPE_DATE = 3;

    private static final int TYPE_DATETIME = 4;

    private static final int TYPE_DOUBLE = 1;

    private static final int TYPE_INTEGER = 2;

    private static final int TYPE_LONG = 6;

    private static final int TYPE_STRING = 0;

    private static final int TYPE_TIME = 5;

    private static String[] columnNames;

    private static List<DataSetRow> dataSetRows;

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getMetadataTables".
     * 
     * @param database
     * @param connection
     * @return
     */
    public static List<MetadataTable> getMetadataTables(MetadataDatabase database, Connection connection) {
        List<MetadataTable> medataTables = new ArrayList<MetadataTable>();
        try {
            DatabaseDiagram dbDiagram = DenguesModelManager.getDatabaseFactory().createDatabaseDiagram();
            getDbTables(dbDiagram, connection, null);
            for (Object obj : dbDiagram.getDbChildren()) {
                if (obj instanceof DBTable) {
                    medataTables.add(MetadataHelper.convertMetadataTable((DBTable) obj));
                }
            }
        } catch (Exception e) {
            log.error("Database Exception", e); //$NON-NLS-1$
        }
        return medataTables;
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getDbTables".
     * 
     * @param diagram
     * @param connection
     * @param monitor
     */
    public static void getDbTables(DatabaseDiagram diagram, Connection connection, IProgressMonitor monitor) {
        if (monitor == null) {
            monitor = new NullProgressMonitor();
        }
        List<String> existTnames = getExistTableNames(diagram);
        EList dbChildren = diagram.getDbChildren();
        SubProgressMonitor submonitor = new SubProgressMonitor(monitor, 1);
        submonitor.beginTask("retrieve tables...", 10);
        try {
            String[] tableTypes = { TABLETYPE_TABLE, TABLETYPE_VIEW, TABLETYPE_SYNONYM };
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rsTables = dbMetaData.getTables(null, DenguesCorePlugin.getDefault().getDenguesUiService()
                    .getConnectivityManager().getSchema(), "%", tableTypes); //$NON-NLS-1$
            int tCount = 0;
            while (rsTables.next()) {
                String tableName = rsTables.getString("TABLE_NAME"); //$NON-NLS-1$
                if (existTnames.contains(tableName)) {
                    continue;
                }
                DBTable table = DenguesModelManager.getDatabaseFactory().createDBTable();
                table.setName(tableName);
                table.setX(10 + (180 * tCount));
                table.setY(80);
                table.setWidth(170);
                table.getColumns().addAll((getDbColumns(dbMetaData, table, submonitor)));
                table.setHeight(26 * (table.getColumns().size() + 1));
                dbChildren.add(table);
                tCount++;
                monitor.worked(1);
            }
            rsTables.close();
        } catch (Exception e) {
            log.error("Database Exception", e); //$NON-NLS-1$
        }
        submonitor.done();
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "autoLinkFKs".
     * 
     * @param diagram
     */
    public static void autoLinkFKs(DatabaseDiagram diagram) {
        EList dbChildren2 = diagram.getDbChildren();
        getColumnsMap(dbChildren2);
        List<ForeignKeyLabel> list = new ArrayList<ForeignKeyLabel>();
        for (Object object2 : dbChildren2) {
            if (object2 instanceof DBTable) {
                DBTable table = (DBTable) object2;
                EList columns = table.getColumns();
                for (Object object3 : columns) {
                    DBColumn object32 = (DBColumn) object3;
                    if (object32.isForeignKey()) {
                        DBColumn tcolumn = columnsMap.get(object32.getProperties().get(PKTABLE_NAME) + "."
                                + object32.getProperties().get(PKCOLUMN_NAME));
                        if (tcolumn != null) {
                            ForeignKey foreignKey = DenguesModelManager.getDatabaseFactory().createForeignKey();
                            foreignKey.setSource(object32);
                            object32.getOutputs().add(foreignKey);
                            foreignKey.setTarget(tcolumn);
                            tcolumn.getInputs().add(foreignKey);
                            foreignKey.setName((String) object32.getProperties().get(FK_NAME));
                            ForeignKeyLabel fkLabel = DenguesModelManager.getDatabaseFactory().createForeignKeyLabel();
                            fkLabel.setName(foreignKey.getName());
                            fkLabel.getProperties().put(FK_NAME, getForeignKeyUname(foreignKey));
                            list.add(fkLabel);

                        }
                    }
                }
            }
        }
        for (ForeignKeyLabel foreignKeyLabel : list) {
            if (!foreigneKeyLabels.contains(foreignKeyLabel.getProperties().get(FK_NAME))) {
                dbChildren2.add(foreignKeyLabel);
            }
        }
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getExistTableNames".
     * 
     * @param diagram
     * @return
     */
    public static List<String> getExistTableNames(DatabaseDiagram diagram) {
        List<String> existTnames = new ArrayList<String>();
        EList dbChildren = diagram.getDbChildren();
        for (Object object : dbChildren) {
            if (object instanceof DBTable) {
                existTnames.add(((DBTable) object).getName());
            }
        }
        return existTnames;
    }

    private static Map<String, DBColumn> columnsMap = new HashMap<String, DBColumn>();

    private static List<String> foreigneKeyLabels = new ArrayList<String>();

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getColumn".
     * 
     * @param dbChildren2
     * @param tName
     * @param cName
     * @return
     */
    public static void getColumnsMap(EList dbChildren2) {
        columnsMap.clear();
        foreigneKeyLabels.clear();
        for (Object object2 : dbChildren2) {
            if (object2 instanceof DBTable) {
                DBTable table = (DBTable) object2;
                EList columns = table.getColumns();
                for (Object object3 : columns) {
                    DBColumn object32 = (DBColumn) object3;
                    columnsMap.put(table.getName() + "." + object32.getName(), object32);
                    EList outputs = object32.getOutputs();
                    for (Object object : outputs) {
                        ForeignKey foreignKey = (ForeignKey) object;
                        foreigneKeyLabels.add(getForeignKeyUname(foreignKey));
                    }
                }
            }
        }
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getForeignKeyUname".
     * 
     * @param key
     * @return
     */
    public static String getForeignKeyUname(ForeignKey key) {
        return key.getSource().getTable().getName() + "." + key.getSource().getName() + "->"
                + key.getTarget().getTable().getName() + "." + key.getTarget().getName();
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "getDbColumns".
     * 
     * @param dbMetaData
     * @param table
     * @param monitor
     * @return
     */
    private static List<DBColumn> getDbColumns(DatabaseMetaData dbMetaData, DBTable table, IProgressMonitor monitor) {
        if (monitor == null) {
            monitor = new NullProgressMonitor();
        }

        SubProgressMonitor submonitor = new SubProgressMonitor(monitor, 1);
        submonitor.beginTask("retrieve table: " + table.getName(), 10);
        List<DBColumn> columns = new ArrayList<DBColumn>();
        try {
            List<String> keyStrings = new ArrayList<String>();
            ResultSet keys = dbMetaData.getPrimaryKeys(null, DenguesCorePlugin.getDefault().getDenguesUiService()
                    .getConnectivityManager().getSchema(), table.getName());
            while (keys.next()) {
                keyStrings.add(keys.getString(COLUMN_NAME)); //$NON-NLS-1$
            }
            keys.close();

            List<String> fkeyStrings = new ArrayList<String>();
            List<String> pkTnames = new ArrayList<String>();
            List<String> pkCnames = new ArrayList<String>();
            List<String> pkNames = new ArrayList<String>();

            ResultSet fkeys = dbMetaData.getImportedKeys(null, DenguesCorePlugin.getDefault().getDenguesUiService()
                    .getConnectivityManager().getSchema(), table.getName());
            // init(fkeys, null, null);
            while (fkeys.next()) {
                fkeyStrings.add(fkeys.getString(FKCOLUMN_NAME)); //$NON-NLS-1$
                pkTnames.add(fkeys.getString(PKTABLE_NAME));
                pkCnames.add(fkeys.getString(PKCOLUMN_NAME));
                pkNames.add(fkeys.getString(FK_NAME));
            }
            fkeys.close();

            ResultSet rsColumns = dbMetaData.getColumns(null, null, table.getName(), null);
            while (rsColumns.next()) {
                DBColumn column = DenguesModelManager.getDatabaseFactory().createDBColumn();
                String colName = rsColumns.getString(COLUMN_NAME); //$NON-NLS-1$
                column.setName(colName);
                column.setType(rsColumns.getString("TYPE_NAME").toUpperCase());
                column.setLength(rsColumns.getInt("COLUMN_SIZE"));
                column.setPrecision(rsColumns.getInt("NUM_PREC_RADIX"));
                column.setNullable(rsColumns.getString("IS_NULLABLE").equalsIgnoreCase("YES"));
                column.setKey(keyStrings.contains(colName));
                int indexOf = fkeyStrings.indexOf(colName);
                column.setForeignKey(indexOf > -1);
                if (indexOf > -1) {
                    column.getProperties().put(PKTABLE_NAME, pkTnames.get(indexOf));
                    column.getProperties().put(PKCOLUMN_NAME, pkCnames.get(indexOf));
                    column.getProperties().put(FK_NAME, pkNames.get(indexOf));
                }
                column.setTable(table);
                columns.add(column);
                submonitor.worked(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        submonitor.done();
        return columns;
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "init".
     * 
     * @param resultSet
     * @param columnLabels
     * @param relevantIndeces
     */
    public static void init(ResultSet resultSet, String[] columnLabels, int[] relevantIndeces) {
        try {
            if (resultSet == null) {
                dataSetRows = new ArrayList<DataSetRow>();
                return;
            }
            ResultSetMetaData metadata = resultSet.getMetaData();

            // create default column indexes
            if (relevantIndeces == null || relevantIndeces.length == 0) {
                relevantIndeces = new int[metadata.getColumnCount()];
                for (int i = 1; i <= metadata.getColumnCount(); i++) {
                    relevantIndeces[i - 1] = i;
                }
            }
            // create column labels
            if (columnLabels != null && columnLabels.length != 0) {
                columnNames = columnLabels;
            } else {
                columnNames = new String[relevantIndeces.length];
                for (int i = 0; i < relevantIndeces.length; i++) {
                    columnNames[i] = metadata.getColumnName(relevantIndeces[i]);
                }
            }
            // create column types
            int[] pcolumnTypes = new int[relevantIndeces.length];

            for (int i = 0; i < relevantIndeces.length; i++) {

                switch (metadata.getColumnType(relevantIndeces[i])) {

                case Types.CHAR:
                case Types.VARCHAR:
                case Types.LONGVARCHAR:
                case -9:
                    pcolumnTypes[i] = TYPE_STRING;
                    break;

                case Types.INTEGER:
                case Types.SMALLINT:
                case Types.TINYINT:
                    pcolumnTypes[i] = TYPE_INTEGER;
                    break;

                case Types.DECIMAL:
                case Types.NUMERIC:
                case Types.DOUBLE:
                case Types.FLOAT:
                case Types.REAL:
                    pcolumnTypes[i] = TYPE_DOUBLE;
                    break;

                case Types.DATE:
                case Types.TIMESTAMP:
                    pcolumnTypes[i] = TYPE_DATETIME;
                    break;

                case Types.TIME:
                    pcolumnTypes[i] = TYPE_TIME;
                    break;

                case Types.BIGINT:
                    pcolumnTypes[i] = TYPE_LONG;
                    break;

                default:
                    pcolumnTypes[i] = TYPE_STRING;
                }
            }
            // create rows
            dataSetRows = new ArrayList<DataSetRow>(100);
            int rowCount = 0;
            while (resultSet.next()) {

                DataSetRow row = new DataSetRow(relevantIndeces.length);
                row.setRowIndex(rowCount);
                rowCount++;
                for (int i = 0; i < relevantIndeces.length; i++) {
                    switch (pcolumnTypes[i]) {
                    case TYPE_STRING:
                        row.setValue(i, resultSet.getString(relevantIndeces[i]));
                        break;
                    case TYPE_INTEGER:
                        row.setValue(i, new Long(resultSet.getInt(relevantIndeces[i])));
                        break;
                    case TYPE_DOUBLE:
                        row.setValue(i, new Double(resultSet.getDouble(relevantIndeces[i])));
                        break;
                    case TYPE_DATE:
                        row.setValue(i, resultSet.getDate(relevantIndeces[i]));
                        break;
                    case TYPE_DATETIME:
                        row.setValue(i, resultSet.getTimestamp(relevantIndeces[i]));
                        break;
                    case TYPE_TIME:
                        row.setValue(i, resultSet.getTime(relevantIndeces[i]));
                        break;
                    case TYPE_LONG:
                        row.setValue(i, new Long(resultSet.getLong(relevantIndeces[i])));
                        break;
                    default:
                        row.setValue(i, resultSet.getString(relevantIndeces[i]));
                        break;
                    }

                    if (resultSet.wasNull()) {
                        row.setValue(i, null);
                    }

                }
                dataSetRows.add(row);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * Getter for columnNames.
     * 
     * @return the columnNames
     */
    public static String[] getColumnNames() {
        return columnNames;
    }

    /**
     * Getter for dataSetRows.
     * 
     * @return the dataSetRows
     */
    public static List<DataSetRow> getDataSetRows() {
        return dataSetRows;
    }

    public static Map<String, DBColumn> getColumnsMap() {
        return columnsMap;
    }

}
